<?php
require_once SHMS_CORE_PATH."/shms_db_common.inc";

function shms_core_db_add_group($group){
  $ts = db_transaction();
  try{
    // first add into group table (assume no duplicate name)
    if(drupal_write_record(SHMS_GROUP_TABLE, $group) != SAVED_NEW){
      throw new Exception("Duplicated CCA Table Record");
    }
    if($group['parent_gid'] == 0) return TRUE;  // done for the root group case
    
    // then add into the group_by_year table for the non-root group
    $group['gid'] = $group['id']; unset($group['id']);
    if(drupal_write_record(SHMS_GROUP_BY_YEAR_TABLE, $group) != SAVED_NEW){
      throw new Exception("Duplicated CCA by Year Table Record");
    }
    return TRUE;
  } catch(exception $e){
    $ts->rollback();
    watchdog("error", "Problem when adding parent group:".$e);
    return FALSE;
  }
}

function shms_core_db_update_group($group){
  $ts = db_transaction();
  try{
    // values submitted:
    
    // first update group table
    if(drupal_write_record(SHMS_GROUP_TABLE, $group,'id') != SAVED_UPDATED){
      throw new Exception("Duplicated CCA Table Record");
    }
    if($group['parent_gid'] == 0) return TRUE;  // done for the root group case
    
    // then update the group_by_year table for the non-root group
    $group['gid'] = $group['id']; unset($group['id']);
    $query = db_update(SHMS_GROUP_BY_YEAR_TABLE);
    if(drupal_write_record(SHMS_GROUP_BY_YEAR_TABLE, $group,'gid') != SAVED_UPDATED){
      throw new Exception("Duplicated CCA by Year Table Record");
    }
    return TRUE;
  } catch(exception $e){
    $ts->rollback();
    return FALSE;
  }
}

/**
 * Load all group information -- basic info, and the by year info
 * @return an associative array structured as:
 *  - basic: the basic info
 *  - by_year: by year info, an academic year keyed array
 */
function shms_core_db_cca_load_all($gid){
  return array(
    'basic' => shms_core_db_load_from_table(SHMS_GROUP_TABLE, 'id', $gid),
    'by_year' => shms_core_db_load_assoc_all_match(SHMS_GROUP_BY_YEAR_TABLE,'gid', $gid, 'id',array('field'=>"year",'dir'=>"DESC")),
  );
}

/**
 * Add member(person by year) into group by year.
 * @param $gyid - the group by year id
 * @param $member_arr
 *    - the associative array has key 'pyid','position_name' for each record
 * @param $type: 'member','subcom'; default to member
 */
function shms_core_db_cca_add_member_into_gy($gyid,$member_arr,$type = "member"){
  $query = db_insert(SHMS_PERSON_GROUP_TABLE)->fields(array('pyid','gyid','position_name','is_com'));
  $is_com = ($type != "member" ? 1 : 0);
  $current_list = shms_core_db_get_person_info_list_from_gyid($gyid);
  foreach($member_arr as $pyid => $position_name){
    if(array_key_exists($pyid,$current_list))  continue; // trim off the existing members.
    $query->values(array('pyid'=>$pyid,'gyid'=>$gyid,'position_name'=>$position_name,'is_com'=>$is_com));
  }
  $query->execute();
  return TRUE;
}

/**
 * Load a list of person in this group from the GYID
 * @param $gyid - the group by year table id
 * @return $person_arr
 *   an associative array of $pyid => $info, who are in this group
 */
function shms_core_db_get_person_info_list_from_gyid($gyid){
  $query = db_select(SHMS_PERSON_GROUP_TABLE,'pgy');
  $query->innerJoin(SHMS_PERSON_BY_YEAR_TABLE,"py","pgy.pyid = py.id");
  $query->innerJoin(SHMS_PERSON_TABLE,'p',"py.pid = p.id");
  $query->condition('pgy.gyid',$gyid);
  $query->fields('p');$query->fields('pgy',array("position_name","cca_points","comments",'is_com'));
  $query->addField('py',"id","pyid");
  $query->addField('pgy',"id","pgyid");
  $query->addField('py',"room_number");
  return $query->execute()->fetchAllAssoc("pyid",PDO::FETCH_ASSOC);
}


/**
 * @param $member_list: an associative array of $pyid => $record, where record
 *  has key 'position_name', 'cca_points', 'comments'
 * @return True on success.
 */
function shms_core_db_cca_update_member_info_by_gyid($member_list){
  $records = array();
  foreach($member_list as $record){$records += $record;}
  
  foreach($records as $pgyid => $record){
    $query = db_update(SHMS_PERSON_GROUP_TABLE);
    $update_arr = array(
      'position_name'=>$record['position_name'],
      'comments'     => $record['comments'],
    );
    if($record['cca_points'] != "") $update_arr['cca_points'] = $record['cca_points'];
    $query->fields( $update_arr);
    $query->condition('id',$pgyid);
    $query->execute();
  }
  return TRUE;
}


/**
 * Remove record of $pyid in $gyid in the {SHMS_PERSON_GROUP_TABLE}.
 */
function shms_db_cca_remove_person_from_group($pyid,$gyid){
  $query = db_delete(SHMS_PERSON_GROUP_TABLE);
  $query->condition('pyid',$pyid);
  $query->condition('gyid',$gyid);
  $query->execute();
  return TRUE;
}

function shms_db_cca_terminate_cca_forever($gid){
  // set a termination date
  db_update(SHMS_GROUP_TABLE)
    ->fields(array('end_time' => time()))
    ->condition('id',$gid)
    ->execute();
}